package com.zx.db.mysql.dbutils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 如果报java.sql.SQLException: The connection property 'zeroDateTimeBehavior' only accepts values of the form: 'exception', 'round' or 'convertToNull'. The value 'convertToNull ' is not in this set.
 * 错，就将jdbcurl中的zeroDateTimeBehavior 等去掉，然后执行。
 * @author Ken
 * @version 2016年3月31日
 */
public class MysqlJDbcUtils {

	private static Logger LOG = LoggerFactory.getLogger(MysqlJDbcUtils.class);

	/**
	 * 获得数据库的连接.
	 * @return 如果发生错误，返回null. 
	 */
	public Connection getConnection() {
		try {
			Class.forName(JdbclInit.getInstance().getDriver());// 加载数据库驱动
			Connection con = DriverManager.getConnection(JdbclInit.getInstance().getUrl(),
					JdbclInit.getInstance().getUsername(), JdbclInit.getInstance().getPassword());
			LOG.debug("connection：{}", con);
			return con;
		} catch (ClassNotFoundException e) {
			LOG.error("get conection error , 请检查数据库是驱动是否已有", e);
		} catch (SQLException e) {
			LOG.error("get conection error", e);
		}
		return null;
	}

	/**
	 * close the connection.
	 * @param connection connection to be closed;
	 * @return true if close ok , else false
	 */
	public boolean closeConnection(Connection connection) {
		try {
			if (connection != null) {
				connection.close();
			}
			return Boolean.TRUE;
		} catch (SQLException e) {
			LOG.error("close conection error", e);
		}
		return Boolean.FALSE;
	}

	/**
	 * 关闭ResultSet
	 * @return 返回关闭状态，true 为关闭.
	 */
	public boolean closeResultSet(ResultSet rst) {
		if (rst != null) {
			try {
				rst.close();
				return Boolean.TRUE;
			} catch (SQLException e) {
				LOG.error("close ResultSet error", e);
			}
		}
		return Boolean.FALSE;
	}

	/**
	 * 关闭statement
	 * @return 返回关闭状态，true 为关闭.
	 */
	public boolean closeStatement(Statement stmt) {
		if (stmt != null) {
			if (stmt instanceof PreparedStatement) {
				try {
					((PreparedStatement) stmt).close();
					return Boolean.TRUE;
				} catch (SQLException e) {
					LOG.error("close PreparedStatement error", e);
				}
			} else {
				try {
					stmt.close();
					return Boolean.TRUE;
				} catch (SQLException e) {
					LOG.error("close Statement error", e);
				}
			}
			return Boolean.FALSE;
		}
		return true;
	}

	/**
	 * 关闭ResultSet rs, Statement stmt, Connection conn
	 */
	public boolean closeAll(ResultSet rs, Statement stmt, Connection conn) {
		boolean rsClose = closeResultSet(rs);
		boolean stClose = closeStatement(stmt);
		boolean cnClose = closeConnection(conn);
		return rsClose && stClose && cnClose;
	}

	/**
	 * 关闭Statement stmt, Connection conn
	 */
	public boolean closeAll(Statement stmt, Connection conn) {
		boolean stClose = closeStatement(stmt);
		boolean cnClose = closeConnection(conn);
		return stClose && cnClose;
	}

	/**
	 * 查询单条记录
	 * @return 返回列为key,值为value的map.
	 * @throws SQLException 如果执行有问题抛出异常.
	 */
	public Map<String, Object> findOne(String sql, List<Object> params) throws SQLException {
		Map<String, Object> map = new HashMap<String, Object>();
		Connection con = null;
		PreparedStatement pt = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			pt = con.prepareStatement(sql);
			if (params != null && !params.isEmpty()) {
				for (int i = 0, index = 1; i < params.size(); i++) {
					pt.setObject(index++, params.get(i));
				}
			}
			rs = pt.executeQuery();//返回查询结果
			ResultSetMetaData metaData = rs.getMetaData();
			int colLen = metaData.getColumnCount();
			while (rs.next()) {
				for (int i = 0; i < colLen; i++) {
					String colsName = metaData.getColumnName(i + 1);
					Object colsValue = rs.getObject(colsName);
					map.put(colsName, colsValue);
				}
				return map;//单条么，这个时候就强行回退.
			}
			return map;
		} finally {
			closeAll(rs, pt, con);
		}
	}

	/**通过反射机制查询单条记录
	 * @param sql
	 * @param params
	 * @param cls
	 * @return
	 * @throws Exception
	 */
	public <T> T findOne(String sql, List<Object> params, Class<T> cls) throws Exception {
		@SuppressWarnings("unchecked")
		T obj = (T) Class.forName(cls.getName());
		Connection con = null;
		PreparedStatement pt = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			pt = con.prepareStatement(sql);
			if (params != null && !params.isEmpty()) {
				for (int i = 0, index = 1; i < params.size(); i++) {
					pt.setObject(index++, params.get(i));
				}
			}
			rs = pt.executeQuery();//返回查询结果
			ResultSetMetaData metaData = rs.getMetaData();
			int colLen = metaData.getColumnCount();
			while (rs.next()) {
				for (int i = 0; i < colLen; i++) {
					String colsName = metaData.getColumnName(i + 1);
					Object colsValue = rs.getObject(colsName);
					Field field = cls.getDeclaredField(colsName);
					field.setAccessible(true); //打开javabean的访问权限
					field.set(obj, colsValue);
				}
			}
		} finally {
			closeAll(rs, pt, con);
		}
		return obj;
	}

	/**查询多条记录
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> findList(String sql, List<Object> params) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Connection con = null;
		PreparedStatement pt = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			pt = con.prepareStatement(sql);
			if (params != null && !params.isEmpty()) {
				for (int i = 0, index = 1; i < params.size(); i++) {
					pt.setObject(index++, params.get(i));
				}
			}
			rs = pt.executeQuery();//返回查询结果
			ResultSetMetaData metaData = rs.getMetaData();
			int colLen = metaData.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 0; i < colLen; i++) {
					String colsName = metaData.getColumnName(i + 1);
					Object colsValue = rs.getObject(colsName);
					map.put(colsName, colsValue);
				}
				list.add(map);
			}
		} finally {
			closeAll(rs, pt, con);
		}

		return list;
	}

	/**通过反射机制查询多条记录
	 * @throws Exception
	 */
	public <T> List<T> findList(String sql, List<Object> params, Class<T> cls) throws Exception {
		List<T> list = new ArrayList<T>();
		Connection con = null;
		PreparedStatement pt = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			pt = con.prepareStatement(sql);
			if (params != null && !params.isEmpty()) {
				for (int i = 0, index = 1; i < params.size(); i++) {
					pt.setObject(index++, params.get(i));
				}
			}
			rs = pt.executeQuery();//返回查询结果
			ResultSetMetaData metaData = rs.getMetaData();
			int colLen = metaData.getColumnCount();
			while (rs.next()) {
				@SuppressWarnings("unchecked")
				T obj = (T) Class.forName(cls.getName());
				for (int i = 0; i < colLen; i++) {
					String colsName = metaData.getColumnName(i + 1);
					Object colsValue = rs.getObject(colsName);
					Field field = cls.getDeclaredField(colsName);
					field.setAccessible(true); //打开javabean的访问权限
					field.set(obj, colsValue);
				}
				if (obj != null) {
					list.add(obj);
				}

			}
		} finally {
			closeAll(rs, pt, con);
		}

		return list;
	}

	/**
	 * 增加、删除、改
	 * 
	 * @return either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothingThrows:
	 * @throws SQLException
	 */
	public int update(String sql, List<Object> params) throws SQLException {
		Connection con = null;
		PreparedStatement pt = null;
		try {
			con = getConnection();
			pt = con.prepareStatement(sql);
			if (params != null && !params.isEmpty()) {
				for (int i = 0, index = 1; i < params.size(); i++) {
					pt.setObject(index++, params.get(i));
				}
			}
			return pt.executeUpdate();
		} finally {
			closeAll(pt, con);
		}
	}

}
